/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Training;
import Course.CourseBean;
import User.UserBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    public static int add(TrainingBean tb)throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("insert into training values(trainingid.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?)");
        pre.setInt(1, tb.getCourseId());
        pre.setString(2, tb.getCompanyName());
        pre.setString(3, tb.getCompanyAddress());
        pre.setString(4, tb.getCompanyPhone());
        pre.setString(5, tb.getCompanyemail());
        pre.setDate(6, tb.getStartTrainingDate());
        pre.setDate(7, tb.getEndTraniningDate());
        pre.setString(8, tb.getTraningTitle());
        pre.setString(9, tb.getTimeFrom());
        pre.setString(10, tb.getTimeTo());
        pre.setString(11, tb.getCoordinator());
        pre.setString(12, tb.getTrainingLocation());
        pre.setFloat(13, tb.getTrainignCost());
        pre.setString(14, tb.getDescription());
        pre.setInt(15, tb.getUserId());
        pre.setInt(16, tb.getNoOfSeats());
        i=pre.executeUpdate();
    }catch(Exception e){
        
    }finally{
        con.close();
    }return i;
    }
    
    public static int edit(TrainingBean tb)throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("update training set  COURSE_ID=?, COMPANY_NAME=?, COMPANY_ADDRESS=?, COMPANY_PHONE=?, COMPANY_EMAIL=?, START_TRAINING_DATE=?, END_TRAINING_DATE=?, TRAINING_TITLE=?, TIME_FROM=?, TIME_TO=?, COORDINATOR=?, TRAINING_LOCATION=?, TRAINING_COST=?, DESCRIPTION=?, USER_ID=?, ENTRY_DATE=sysdate, NO_OF_SEATS=? where TRAINING_ID=?");
        pre.setInt(1, tb.getCourseId());
        pre.setString(2, tb.getCompanyName());
        pre.setString(3, tb.getCompanyAddress());
        pre.setString(4, tb.getCompanyPhone());
        pre.setString(5, tb.getCompanyemail());
        pre.setDate(6, tb.getStartTrainingDate());
        pre.setDate(7, tb.getEndTraniningDate());
        pre.setString(8, tb.getTraningTitle());
        pre.setString(9, tb.getTimeFrom());
        pre.setString(10, tb.getTimeTo());
        pre.setString(11, tb.getCoordinator());
        pre.setString(12, tb.getTrainingLocation());
        pre.setFloat(13, tb.getTrainignCost());
        pre.setString(14, tb.getDescription());
        pre.setInt(15, tb.getUserId());
        pre.setInt(16, tb.getNoOfSeats());
        pre.setInt(17, tb.getTrainingId());
        i=pre.executeUpdate();
    }catch(Exception e){
        
    }finally{
        con.close();
    }return i;
    }
    public static int delete(int id)throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("delete from TRAINING where TRAINING_ID=?");
        pre.setInt(1, id);
        i=pre.executeUpdate();
    }catch(Exception e){
        System.out.println(e);
    }finally{con.close();}return i;
    }

    public static Vector view() throws SQLException{
        Vector<Object> list=new Vector<Object>();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from training t left join course c on t.course_id=c.course_id left join users u on t.user_id=u.user_id");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                TrainingBean tb=new TrainingBean();
                CourseBean cb=new CourseBean();
                UserBean ub=new UserBean();
                tb.setTrainingId(rs.getInt(1));
                tb.setCourseId(rs.getInt(2));
                tb.setCompanyName(rs.getString(3));
                tb.setCompanyAddress(rs.getString(4));
                tb.setCompanyPhone(rs.getString(5));
                tb.setCompanyemail(rs.getString(6));
                tb.setStartTrainingDate(rs.getDate(7));
                tb.setEndTraniningDate(rs.getDate(8));
                tb.setTraningTitle(rs.getString(9));
                tb.setTimeFrom(rs.getString(10));
                tb.setTimeTo(rs.getString(11));
                tb.setCoordinator(rs.getString(12));
                tb.setTrainingLocation(rs.getString(13));
                tb.setTrainignCost(rs.getFloat(14));
                tb.setDescription(rs.getString(15));
                tb.setUserId(rs.getInt(16));
                tb.setEntryDate(rs.getDate(17));
                tb.setNoOfSeats(rs.getInt(18));
                cb.setCourseId(rs.getInt(19));
                cb.setCourseName(rs.getString(20));
                cb.setCourseType(rs.getInt(21));
                cb.setCoursePeriod(rs.getInt(22));
                cb.setStream(rs.getString(23));
                cb.setEntryDate(rs.getDate(24));
                tb.setCourseBean(cb);
                ub.setUserId(rs.getInt(25));
                ub.setDor(rs.getDate(26));
                ub.setPassword(rs.getString(27));
                ub.setUserType(rs.getString(28));
                ub.setEmail(rs.getString(29));
                ub.setEmployeeName(rs.getString(30));
                ub.setDob(rs.getDate(31));
                ub.setEntryDate(rs.getDate(32));
                ub.setUserStatus(rs.getInt(33));
                ub.setEntryEmail(rs.getString(34));
                tb.setUserBean(ub);
                list.add(tb);
            }   
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return list;
    }
    public static TrainingBean viewSpecific(String cname, String val) throws SQLException{
            TrainingBean tb=new TrainingBean();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from training t left join course c on t.course_id=c.course_id left join users u on t.user_id=u.user_id where "+cname+"="+val+"");
            ResultSet rs=pre.executeQuery();
            while(rs.next()){
                CourseBean cb=new CourseBean();
                UserBean ub=new UserBean();
                tb.setTrainingId(rs.getInt(1));
                tb.setCourseId(rs.getInt(2));
                tb.setCompanyName(rs.getString(3));
                tb.setCompanyAddress(rs.getString(4));
                tb.setCompanyPhone(rs.getString(5));
                tb.setCompanyemail(rs.getString(6));
                tb.setStartTrainingDate(rs.getDate(7));
                tb.setEndTraniningDate(rs.getDate(8));
                tb.setTraningTitle(rs.getString(9));
                tb.setTimeFrom(rs.getString(10));
                tb.setTimeTo(rs.getString(11));
                tb.setCoordinator(rs.getString(12));
                tb.setTrainingLocation(rs.getString(13));
                tb.setTrainignCost(rs.getFloat(14));
                tb.setDescription(rs.getString(15));
                tb.setUserId(rs.getInt(16));
                tb.setEntryDate(rs.getDate(17));
                tb.setNoOfSeats(rs.getInt(18));
                cb.setCourseId(rs.getInt(19));
                cb.setCourseName(rs.getString(20));
                cb.setCourseType(rs.getInt(21));
                cb.setCoursePeriod(rs.getInt(22));
                cb.setStream(rs.getString(23));
                cb.setEntryDate(rs.getDate(24));
                tb.setCourseBean(cb);
                ub.setUserId(rs.getInt(25));
                ub.setDor(rs.getDate(26));
                ub.setPassword(rs.getString(27));
                ub.setUserType(rs.getString(28));
                ub.setEmail(rs.getString(29));
                ub.setEmployeeName(rs.getString(30));
                ub.setDob(rs.getDate(31));
                ub.setEntryDate(rs.getDate(32));
                ub.setUserStatus(rs.getInt(33));
                ub.setEntryEmail(rs.getString(34));
                tb.setUserBean(ub);
            }   
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return tb;
    }
}
